package com.ty.dao.impl;

import com.ty.bean.Role;
import com.ty.bean.Users;
import com.ty.dao.DBUtils;
import com.ty.dao.UsersDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 田野
 */
public class UsersDaoImpl extends DBUtils implements UsersDao {
    @Override
    public Users login(String username, String password) {
        Users users = null;
        try {
            String sql = "select * from users where loginName = ? and password = ?";
            ArrayList arrayList = new ArrayList();
            arrayList.add(username);
            arrayList.add(password);
            resultSet = query(sql, arrayList);
            if (resultSet==null){
                return null;
            }
            while (resultSet.next()){
                users = new Users();
                users.setLoginName(username);
                users.setRealName(resultSet.getString("realname"));
                users.setUserId(resultSet.getInt("userid"));
                users.setRoleId(resultSet.getInt("roleid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }

        return users;

    }

    @Override
    public List<Users> getUsersList(int pageIndex, int pageSize) {
        List params = new ArrayList();
        params.add((pageIndex-1)*pageSize);
        params.add(pageSize);

        List<Users> usersList = new ArrayList<>();


        try {
            // 双表联查
            String sql = " select userid, loginname, realname, rolename from users u, role r where u.roleid=r.roleid " +
                    " limit ?, ? ";
            resultSet = query(sql, params);

            while (resultSet.next()){
                Users users = new Users();
                // 取出各表的数据
                // users表
                users.setUserId(resultSet.getInt("userid"));
                users.setLoginName(resultSet.getString("loginname"));
                users.setRealName(resultSet.getString("realname"));
                // role表
                Role role = new Role();
                role.setRoleName(resultSet.getString("rolename"));
                // 建立关系
                users.setRole(role);
                // 添加到users的list集合中并返回
                usersList.add(users);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return usersList;
    }

    @Override
    public int total() {
        int total = 0;

        try {
            String sql = " select count(*) from users u, role r where u.roleid=r.roleid ";
            resultSet = query(sql, null);

            while (resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return total;
    }

    @Override
    public List<Role> getRoleList() {

        List<Role> roleList = new ArrayList<>();

        try {
            String sql = " select * from role ";

            resultSet = query(sql, null);

            while (resultSet.next()){
                Role role = new Role();

                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));

                roleList.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return roleList;
    }

    @Override
    public int insertUser(Users users) {
        int update = 0;
        List params = new ArrayList();

        try {
            String sql = "insert into users values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            params.add(users.getLoginName());
            params.add(users.getPassword());
            params.add(users.getRealName());
            params.add(users.getSex());
            params.add(users.getEmail());
            params.add(users.getAddress());
            params.add(users.getPhone());
            params.add(users.getCardId());
            params.add(users.getDesc());
            params.add(users.getRoleId());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }
}















